/*********************************************************************************
 *      Copyright:  (C) 2023 iot<iot@email.com>
 *                  All rights reserved.
 *
 *       Filename:  sql_serve.c
 *    Description:  This file is the use of the interface to the SQliite3
 *                 
 *        Version:  1.0.0(03/21/23)
 *         Author:  iot <iot@email.com>
 *      ChangeLog:  1, Release initial version on "03/21/23 02:06:58"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>

#include "sql_serve.h"
#include "sqlite3.h"
#include "logger.h"

static sqlite3    *db=NULL;

/* creat a database */
int creat_db(char *db_name)
{
	char		*zErrMsg = NULL;
	char        buf[256];

	if( !db_name )
	{
		log_error("error:invalid input argument\n");
		return -1;
	}

	if( SQLITE_OK != sqlite3_open(db_name,&db) )
	{
		log_error("error open database:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_info("open database successful\n");
			
		memset(buf,0,sizeof(buf));
		snprintf(buf, sizeof(buf),"CREATE TABLE IF NOT EXISTS EX%s(ID INTEGER PRIMARY KEY AUTOINCREMENT, SN CHAR(20),DATIME CHAR(100),TEMPERATURE CHAR(30));", TABLE_NAME);
		log_info("message:%s\n",buf);

		if( SQLITE_OK != sqlite3_exec(db,buf,callback,0,&zErrMsg) )
    	{
        	log_error("creat table error:%s\n",zErrMsg);
        	sqlite3_free(zErrMsg);
        	return -3;
    	}
    	else
    	{
        	log_info("creat table successfully\n");
        	return 0;
    	}

		return 0;
	}
}

/*callback function*/
int callback(void *NotUsed,int argc,char **argv,char **azColName)
{
	int i;

	for( i=0;i<argc;i++ )
	{
		log_info("%s = %s\n",azColName[i],argv[i] ? argv[i] : "NULL");
	}
	return 0;
}


/*insert table into the database*/
int insert_db(char *buf_data[])
{
	char		buf_insert[256];
	char		*zErrMsg = NULL;

	memset(buf_insert,0,sizeof(buf_insert));
	snprintf(buf_insert,sizeof(buf_insert),"INSERT INTO %s VALUES (NULL,'%s','%s','%s');",TABLE_NAME,buf_data[0],buf_data[1],buf_data[2]);

	if( SQLITE_OK != sqlite3_exec(db,buf_insert,callback,0,&zErrMsg) )
	{
		log_error("sqlite insert table error:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	else
	{
		log_info("insert table successfully\n");
		return 0;
	}

}

/*close the database*/
int close_db(void)
{
	char *zErrMsg = NULL;

	if( SQLITE_OK != sqlite3_close(db) )
	{
		log_error("error close database:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	else
	{
		log_info("successfully close database\n");
		return 0;
	}
}
